//
//  DetailDao.swift
//  KoiAccounting
//
//  Created by 纪洪文 on 22.03.25.
//

import Foundation
import GRDB

class DetailDao {
    let dbQueue: DatabaseQueue
    
    init(dbQueue: DatabaseQueue) {
        self.dbQueue = dbQueue
    }
    
    func saveRecord(accountingDetail: AccountingDetail) {
        var accountingDetail = accountingDetail
        accountingDetail.recordId = ShortUUID().generate()
        do {
            try dbQueue.write { db in
                try accountingDetail.insert(db)
            }
        } catch {
            logger.error("saveRecord fail \(error)")
        }
    }
    
    func getDetailRecord(recordId: String?, uid: String, bookId: String) -> AccountingDetail? {
        guard let recordId = recordId else { return nil }
        do {
            return try dbQueue.read { db in
                try AccountingDetail
                    .filter(AccountingDetail.Columns.uid == uid)
                    .filter(AccountingDetail.Columns.bookId == bookId)
                    .filter(AccountingDetail.Columns.recordId == recordId)
                    .fetchOne(db)
            }
        } catch {
            logger.error("getDetailRecord fail \(error)")
        }
        return nil
    }
    
    func updateRecord(detail: AccountingDetail?) {
        guard var detail = detail else { return }
        detail.makeUpdate()
        do {
            try dbQueue.write { db in
                try detail.update(db)
            }
        } catch {
            logger.error("updateRecord fail \(error)")
        }
    }
    
    func delete(recordId: String?, uid: String, bookId: String) {
        guard let recordId = recordId else { return }
        do {
            try dbQueue.write { db in
                var detail = try AccountingDetail
                    .filter(AccountingDetail.Columns.uid == uid)
                    .filter(AccountingDetail.Columns.bookId == bookId)
                    .filter(AccountingDetail.Columns.recordId == recordId)
                    .fetchOne(db)
                
                detail?.makeDelete()
                try detail?.upsert(db)
            }
        } catch {
            logger.error("saveRecord fail \(error)")
        }
    }
    
    func getOverviewAccountingData(month:String, uid:String, bookId:String) -> OverviewAreaAccountingData?{
        var overviewAccountingData = OverviewAreaAccountingData()
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: "select category_type, sum(amount) as amount_total from accounting_detail_combine_view where strftime('%Y-%m',datetime(date,'localtime')) = ? and uid = ? and book_id =? group by category_type", arguments: [month,uid,bookId])
                var categoryAmount = [String : Double]()
                while let row = try rows.next() {
                    let categoryType: String = row["category_type"]
                    categoryAmount[categoryType] = row["amount_total"]
                }
                let expenseAmount: Double = categoryAmount["expense"] ?? 0
                let incomeAmount: Double = categoryAmount["income"] ?? 0
                overviewAccountingData.expenseAmount = expenseAmount
                overviewAccountingData.incomeAmount = incomeAmount
                overviewAccountingData.balanceAmount = incomeAmount - expenseAmount
                return overviewAccountingData
            }
        } catch {
            logger.error("getOverviewAccountingData fetch db fail \(error)")
            return nil
        }
    }
    
    func getAccountDetailListByMonth(month:String, uid:String, bookId:String) -> Array<AccountingDetailRowGroup>{
        let dateList = getAccountDetailByMonthGroupByDay(month: month, uid: uid, bookId: bookId)
        let detailList = getAccountDetailByMonth(month: month, uid: uid, bookId: bookId)
        return groupResultList(dateList: dateList, detailList: detailList)
    }
    
    private func groupResultList(dateList: Array<AccountingDateGroupData>, detailList: Array<AccountingDetailCombineView>) -> Array<AccountingDetailRowGroup> {
        var resultList = Array<AccountingDetailRowGroup>()
        var dateMap = [String: [String: Double]]()
        var dateKeyList = Array<String>()
        for dateItem in dateList {
            if (dateMap[dateItem.date] == nil) {
                dateMap[dateItem.date] = [String:Double]()
                dateKeyList.append(dateItem.date)
            }
            let amount = dateMap[dateItem.date]?[dateItem.categoryType] ?? 0.0
            dateMap[dateItem.date]?[dateItem.categoryType] = amount + dateItem.amountTotal
        }
        // 组装日期数据
        
        var detailDateMap = [String: Array<AccountingDetailCombineView>]()
        var recordIdList = Array<String>()
        for detail in detailList {
            let recordId = detail.recordId
            recordIdList.append(recordId.orEmpty)
            if (detailDateMap.contains{$0.key == detail.dateGroupFormat}) {
                detailDateMap[detail.dateGroupFormat.orEmpty]?.append(detail)
            } else {
                detailDateMap[detail.dateGroupFormat.orEmpty] = Array()
                detailDateMap[detail.dateGroupFormat.orEmpty]?.append(detail)
            }
        }
        // 按天插入最终数据
        
        for (index, date) in dateKeyList.enumerated() {
            let expenseAmount = dateMap[date]?["expense"] ?? 0
            let incomeAmount = dateMap[date]?["income"] ?? 0
            let detailGroupDayList = detailDateMap[date] ?? Array<AccountingDetailCombineView>()
            var groupData = Array<AccountingDetailCombine>()
            for detailView in detailGroupDayList {
                groupData.append(detailView.getAccountingDetailCombine())
            }
            resultList.append(AccountingDetailRowGroup(id: index, date: date, dateOriginal: date, expenseAmount: expenseAmount, incomeAmount: incomeAmount, groupData: groupData))
        }
        return resultList
    }
    
    private func getAccountDetailByMonth(month:String, uid:String, bookId:String) -> Array<AccountingDetailCombineView>{
        var list = Array<AccountingDetailCombineView>()
        do {
            try dbQueue.read{ db in
                let rows = try Row.fetchCursor(db, sql: "select * from accounting_detail_combine_view where uid = ? and book_id = ? and strftime('%Y-%m',datetime(date,'localtime')) = ? order by date desc", arguments: [uid, bookId, month])
                while let row = try rows.next() {
                    list.append(AccountingDetailCombineView.create(row:row))
                }
            }
        } catch {
            logger.error("getAccountDetailByMonth fail \(error)")
        }
        return list
    }
    
    private func getAccountDetailByMonthGroupByDay(month:String, uid:String, bookId:String) -> Array<AccountingDateGroupData> {
        var resultList = Array<AccountingDateGroupData>()
        do {
            try dbQueue.read{ db in
                let rows = try Row.fetchCursor(db, sql: "select coalesce(strftime('%Y-%m-%d',datetime(date,'localtime')),'') as date, coalesce(category_type,'') as category_type, coalesce(sum(amount),0) as amount_total from (select date ,category_type, sum(amount) as amount from accounting_detail_combine_view where uid = ? and book_id = ? and strftime('%Y-%m',datetime(date,'localtime')) = ? group by strftime('%Y-%m-%d',datetime(date,'localtime')), category_type order by date desc) group by date,category_type order by date desc", arguments: [uid, bookId, month])
                
                while let row = try rows.next() {
                    resultList.append(AccountingDateGroupData.create(row: row))
                }
            }
        } catch {
            logger.error("getAccountDetailByMonthGroupByDay fetch db fail \(error)")
        }
        return resultList
    }
    
    
    func transferCategory(uid: String, bookId: String, oldCid: String , newCid: String) {
        do {
            try dbQueue.write { db in
                try db.execute(sql: "update accounting_detail set cid = ? where uid = ? and book_id = ? and cid = ?" , arguments: [newCid, uid, bookId, oldCid])
            }
        } catch {
            logger.error("transferCategory fetch fail \(error)")
        }
    }
    
    func getWeekDetailListByCid(weekDays: Array<String>, cid: String, uid: String, bookId: String, orderBy: String) -> [AccountingDetailCombine] {
        var resultList = [AccountingDetailCombine]()
        do {
            let weekDaysArgs = weekDays.map{ _ in "?"}.joined(separator: ",")
            let sql = "select * from accounting_detail_combine_view where uid = ? and book_id = ? and cid = ? and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs)) order by \(orderBy) desc"
            let arguments: [DatabaseValueConvertible] = [uid, bookId, cid] + weekDays
            try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    resultList.append(AccountingDetailCombineView.create(row: row).getAccountingDetailCombine())
                }
            }
        } catch {
            logger.error("getWeekDetailListByCid fetch fail \(error)")
        }
        return resultList
    }
    
    func getMonthDetailListByCid(month: String, cid: String, uid: String, bookId: String, orderBy: String) -> [AccountingDetailCombine] {
        var resultList = [AccountingDetailCombine]()
        do {
            let sql = "select * from accounting_detail_combine_view where uid = ? and book_id = ? and cid = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ? order by \(orderBy) desc"
            let arguments: [DatabaseValueConvertible] = [uid, bookId, cid, month]
            try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    resultList.append(AccountingDetailCombineView.create(row: row).getAccountingDetailCombine())
                }
            }
        } catch {
            logger.error("getMonthDetailListByCid fetch fail \(error)")
        }
        return resultList
    }
    
    func getYearDetailListByCid(year: String, cid: String, uid: String, bookId: String, orderBy: String) -> [AccountingDetailCombine] {
        var resultList = [AccountingDetailCombine]()
        do {
            let sql = "select * from accounting_detail_combine_view where uid = ? and book_id = ? and cid = ? and strftime('%Y', datetime(date, 'localtime')) = ? order by \(orderBy) desc"
            let arguments: [DatabaseValueConvertible] = [uid, bookId, cid, year]
            try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    resultList.append(AccountingDetailCombineView.create(row: row).getAccountingDetailCombine())
                }
            }
        } catch {
            logger.error("getYearDetailListByCid fetch fail \(error)")
        }
        return resultList
    }
    
    func getCategoryAmountList(month: String, uid: String, bookId: String, cidList: [String] ) -> [(cid: String?, amount: Double?)] {
        var result = [(cid: String?, amount: Double?)]()
        if cidList.isEmpty {
            return result
        }
        do {
            let cidArgs = cidList.map { _ in "?"}.joined(separator: ",")
            let sql = "select sum(amount) as amount, cid from accounting_detail where uid = ? and book_id = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ? and cid in (\(cidArgs)) group by cid"
            
            let arguments = [uid, bookId, month] + cidList
            try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    result.append((
                        cid: row["cid"] as String?,
                        amount: row["amount"] as Double?
                    ))
                }
            }
        } catch {
            logger.error("getCategoryExpenseAmount fail \(error)")
        }
        return result
    }
    
    func getPushData(pageSize:Int, offset:Int, uid: String, bookId: String) -> Array<AccountingDetail> {
        do {
            return try dbQueue.read { db in
                try AccountingDetail
                    .filter(AccountingDetail.Columns.uid == uid)
                    .filter(AccountingDetail.Columns.bookId == bookId)
                    .filter(AccountingDetail.Columns.dataFlag != DataFlag.synced.rawValue)
                    .limit(pageSize, offset: offset)
                    .fetchAll(db)
            }
            
        } catch {
            logger.error("getPushData fail \(error)")
        }
        return [AccountingDetail]()
    }
    
    func updateSync(list: Array<AccountingDetail>) throws {
        try dbQueue.write { db in
            for detail in list {
                var detail = detail
                try detail.insert(db, onConflict: .replace)
            }
        }
    }
    
    func achievementTotalNumber(uid: String) -> Int {
        do {
            return try dbQueue.read { db in
                try Int.fetchOne(db, sql: "select count(record_id) from accounting_detail where uid = ? and is_del <> 1", arguments: [uid]) ?? 0
                
            }
        } catch {
            logger.error("achievementTotalNumber fail \(error)")
        }
        return 0
    }
    
    func achievementDay(uid: String) -> Double {
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: "select strftime('%s', datetime(create_time, 'localtime')) as create_time from accounting_detail where uid = ? and is_del <> 1 order by create_time asc limit 1", arguments: [uid]) ?? 0
                
            }
        } catch {
            logger.error("achievementTotalNumber fail \(error)")
        }
        return 0
    }
}
